package com.aspire.core;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.configuration.ConfigurationException;
import org.apache.commons.configuration.PropertiesConfiguration;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.log4j.Logger;

public class CreateUAServiceImpl {

	public static Logger logger_db = Logger.getLogger("database");

	/**
	 * 读取数据库通用方法
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	private List<Map<String, Object>> common_select(String sql, Connection conn)
			throws SQLException {
		QueryRunner qr = new QueryRunner();
		List<Map<String, Object>> result = null;
		try {

			result = (List<Map<String, Object>>) qr.query(conn, sql,
					new MapListHandler());

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//conn.close();
			//conn = null;
		}

		return result;
	}

	public List<Map<String, Object>> getFilterDeviceList() throws ConfigurationException, ClassNotFoundException {
		String sql = "select FULLDEVICEID from MM_DEVICEID group by FULLDEVICEID";
		List<Map<String, Object>> result = null;
		try {
			result = common_select(sql, getConnection2());// 使用MM第二数据源
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return result;
	}

	public Map<String, Object> getDeviceUA(int deviceid) throws ConfigurationException, ClassNotFoundException {
		String sql = "select * from t_device_ua where device_id=" + deviceid
				+ " order by length(device_os_ua)";
		List<Map<String, Object>> result = null;
		try {
			result = common_select(sql, getConnection());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		if (result != null && !result.isEmpty()) {
			return result.get(0);
		}

		return null;
	}

	public List<Map<String, Object>> getDeviceUA() throws ConfigurationException, ClassNotFoundException {
		String sql = "select * from t_device_ua order by length(device_os_ua),length(device_ua)";
		List<Map<String, Object>> result = null;
		try {
			result = common_select(sql, getConnection());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return result;
	}

	public List<Map<String, Object>> getDeviceGWUA() throws ConfigurationException, ClassNotFoundException {
		String sql = "select * from t_device_gw_ua";
		List<Map<String, Object>> result = null;
		try {
			result = common_select(sql, getConnection());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return result;
	}

	public List<Map<String, Object>> getDeviceKeyWord() throws ConfigurationException, ClassNotFoundException {
		String sql = "select device_id,keyword1,keyword2,keyword3 from t_device_keyword where (length(keyword1)>3) or (length(keyword1)<=3 and keyword3!='%') order by length(keyword1) desc,length(keyword2) desc,length(keyword3) desc";
		List<Map<String, Object>> result = null;
		try {
			result = common_select(sql, getConnection());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return result;
	}

	public List<Map<String, Object>> getDevice() throws ConfigurationException, ClassNotFoundException {
		String sql = "select series_id,device_id,device_name,device_desc,device_first_alpha,brand_id,english_device_name,english_device_desc,os_id,os_detail,is_touch_screen,screensize,preview_pic_www from t_device order by english_device_name";
		//String sql = "select * from t_device order by english_device_name";
		List<Map<String, Object>> result = null;
		try {
			result = common_select(sql, getConnection());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return result;
	}
	

	public List<Map<String, Object>> getDeviceBrand() throws ConfigurationException, ClassNotFoundException {
		String sql = "select brand_name,brand_id,brand_desc,english_brand_name from t_device_brand order by brand_name";
		List<Map<String, Object>> result = null;
		try {
			result = common_select(sql, getConnection());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return result;
	}

	public List<Map<String, Object>> getDeviceOS() throws ConfigurationException, ClassNotFoundException {
		String sql = "select os_id,osname,osvendor from t_device_os";
		List<Map<String, Object>> result = null;
		try {
			result = common_select(sql, getConnection());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return result;
	}

	public List<Map<String, Object>> getDeviceSeries() throws ConfigurationException, ClassNotFoundException {
		String sql = "select series_id,series_os_id,eng_series_desc from t_device_series";
		List<Map<String, Object>> result = null;
		try {
			result = common_select(sql, getConnection());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return result;
	}
	
	
	
	
	private Connection m_conn = null;

	public Connection getConnection() throws ClassNotFoundException,
			ConfigurationException, SQLException {
		if (m_conn == null) {

			PropertiesConfiguration config = new PropertiesConfiguration(
					"database.properties");

			Class.forName(config.getString("oracle.db.driver"));
			Connection conn = DriverManager.getConnection(
					config.getString("oracle.db.connUrl"),
					config.getString("oracle.db.username"),
					config.getString("oracle.db.password"));
			m_conn = conn;
		}

		return m_conn;
	}
	
	private Connection m_conn2 = null;

	//filter
	public Connection getConnection2() throws ClassNotFoundException,
			ConfigurationException, SQLException {
		if (m_conn2 == null) {

			PropertiesConfiguration config = new PropertiesConfiguration(
					"database.properties");

			Class.forName(config.getString("oracle.db.driver"));
			Connection conn = DriverManager.getConnection(
					config.getString("oracle.filterdb.connUrl"),
					config.getString("oracle.filterdb.username"),
					config.getString("oracle.filterdb.password"));
			m_conn2 = conn;
		}

		return m_conn2;
	}
	
	
	
}
